﻿--|--------------------------------------------------------------------------------
--| [GetUnusedEntityListByGameID] - Insert Procedure Script for Entity
--|--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id (N'[dbo].[GetUnusedEntityListByGameID]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) 
  DROP PROCEDURE [dbo].[GetUnusedEntityListByGameID]
GO

CREATE PROCEDURE [dbo].[GetUnusedEntityListByGameID]
(
	@GameID int
)
AS
	SET NOCOUNT ON

	DECLARE @TeamID int
	SELECT @TeamID = TeamID FROM Game WHERE ID = @GameID
	
	SELECT
		E.[ID],
		E.[Name],
		E.[Description],
		E.[TeamID],
		E.[EntityTypeID],
		ET.[Name] as [EntityTypeName]
	FROM [Entity] E
	LEFT JOIN [EntityType] ET ON E.[EntityTypeID] = ET.[ID]
	LEFT JOIN [GameEntity] GE ON E.ID = GE.EntityID AND GE.[GameID] = @GameID
	WHERE E.TeamID = @TeamID
	AND GE.GameID IS NULL
		
	
GO
 